

-- delete from Menu_Master where Menu_Type='QA'
-- truncate table QA_Agent_Master
-- truncate table QA_AgentDetails
-- truncate table QA_Assign_Log
-- truncate table QA_Assign_Log_ByTL
-- truncate table QA_MonitoringFormDetails
-- truncate table QA_Page_Master
-- truncate table QA_Page_Master1
-- truncate table QA_Parameter_Master
-- truncate table QA_Process_Logic



SET IDENTITY_INSERT [dbo].[Menu_Master] ON 
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (901, N'../QA/Parameters.aspx', N'Parameter Master', N'QA', 1, NULL, 1, NULL, N'~/images/MenuIcons/Qaform.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (902, N'../QA/QualityPageMaster.aspx', N'Questions Master', N'QA', 2, NULL, 1, NULL, N'~/images/MenuIcons/Questions.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (903, N'../QA/QAmanager.aspx', N'QA Manager', N'QA', 4, NULL, 1, NULL, N'~/images/MenuIcons/shift_master.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (904, N'../QA/QA_Agent.aspx', N'QA Agent', N'QA', 5, NULL, 1, NULL, N'~/images/MenuIcons/shift_master.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (905, N'../QA/QA_TL.aspx', N'QA TL', N'QA', 6, NULL, 1, NULL, N'~/images/MenuIcons/shift_master.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (906, N'../QA/report.aspx', N'QA Report', N'QA', 7, NULL, 1, NULL, N'~/images/MenuIcons/sheduled_Report.png')
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (907, N'../QA/QA_Admin_Setup_Master.aspx', N'QA_Admin_Setup_Master', N'QA', NULL, NULL, 1, NULL, NULL)
INSERT [dbo].[Menu_Master] ([Form_Id], [Form_Url], [Menu_Name], [Menu_Type], [Order_No], [Sub_Menu_Type], [RegisteredModule], [Sub_Menu_Name], [Left_Icon_Url]) VALUES (908, N'../QA/QualityMonitoringForm.aspx', N'QualityMonitoringForm', NULL, NULL, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Menu_Master] OFF




/****** Object:  Table [dbo].[QA_Agent_Master]    Script Date: 25/06/2021 04:20:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Agent_Master](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Qa_Agent] [varchar](30) NULL,
	[Agent_Name] [varchar](30) NULL,
	[Created_Date] [datetime] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_AgentDetails]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_AgentDetails](
	[Q_id] [int] IDENTITY(1,1) NOT NULL,
	[P_id] [int] NULL,
	[Agent_Name] [varchar](50) NULL,
	[Auditor_Name] [varchar](50) NULL,
	[Customer_Name] [varchar](50) NULL,
	[Date_of_Call] [datetime] NULL,
	[Date_of_Audit] [datetime] NULL,
	[Mobile] [varchar](12) NULL,
	[Brand] [varchar](50) NULL,
	[Month] [varchar](10) NULL,
	[AHT] [varchar](15) NULL,
	[External_Performance] [int] NULL,
	[Internal_Performance] [int] NULL,
	[Fatal_Error_Call] [int] NULL,
	[call_summary] [varchar](max) NULL,
	[areas_of_strength] [varchar](max) NULL,
	[areas_of_development] [varchar](max) NULL,
	[RecordingUrl] [varchar](400) NULL,
	[CallerEMPID] [varchar](20) NULL,
	[TLname] [varchar](50) NULL,
	[Disposition] [varchar](15) NULL,
	[ManagerName] [varchar](50) NULL,
	[LeadID] [varchar](10) NULL,
	[LeadStatusTime] [varchar](12) NULL,
	[Audit] [varchar](50) NULL,
	[CallDuration] [varchar](10) NULL,
	[Week] [varchar](10) NULL,
	[LeadSource] [varchar](50) NULL,
	[EvaluationMode] [varchar](50) NULL,
	[ACPT] [varchar](50) NULL,
	[log_id] [varchar](50) NULL,
	[FTR] [varchar](50) NULL,
	[Call_Class] [varchar](50) NULL,
	[Verifier_Code] [varchar](50) NULL,
	[No_Of_Call_Recordings] [varchar](50) NULL,
	[TL_Remarks] [varchar](500) NULL,
	[time_stamp] [timestamp] NOT NULL,
	[FeedbackDone] [int] NULL,
	[WrongDisposition] [int] NULL,
	[ShowInTLDashboard] [bit] NULL,
	[Recoeding_path] [nvarchar](300) NULL,
 CONSTRAINT [PK_QA_AgentDetails] PRIMARY KEY CLUSTERED 
(
	[Q_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Assign_Log]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Assign_Log](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Log_Id] [varchar](100) NULL,
	[Manager] [varchar](30) NULL,
	[Assign_To] [varchar](30) NULL,
	[Assign_date] [varchar](50) NULL,
	[Process_ID] [int] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Assign_Log_ByTL]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Assign_Log_ByTL](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Log_Id] [varchar](100) NULL,
	[TL] [varchar](30) NULL,
	[Assign_To] [varchar](30) NULL,
	[Assign_date] [varchar](50) NULL,
	[Process_ID] [int] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_MonitoringFormDetails]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_MonitoringFormDetails](
	[Q_id] [int] NULL,
	[p_id] [int] NULL,
	[Parameter_Name] [varchar](100) NULL,
	[status] [varchar](15) NULL,
	[Question] [varchar](800) NULL,
	[Weightage] [decimal](5, 1) NULL,
	[Applicable_Weightage] [decimal](5, 1) NULL,
	[Agents_Grade] [decimal](5, 1) NULL,
	[Rating] [varchar](10) NULL,
	[QA_comment] [text] NULL,
	[Order_By] [int] NULL,
	[WrongDisposition] [varchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Page_Master]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Page_Master](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[process_id] [int] NULL,
	[parameter_Name] [varchar](100) NULL,
	[status] [varchar](15) NULL,
	[Question] [varchar](800) NULL,
	[Weightage] [decimal](3, 1) NULL,
	[isparameterwisefatal] [bit] NULL,
	[time_stamp] [timestamp] NOT NULL,
 CONSTRAINT [PK__QA_Page___3213E83F6C2966D4] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Page_Master1]    Script Date: 25/06/2021 04:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Page_Master1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[process_id] [int] NULL,
	[CallStatus] [varchar](300) NULL,
	[SubCallStatus] [varchar](3000) NULL,
	[Qa_Start_Date] [date] NULL,
	[QAProcess_ID] [int] NULL,
	[isparameterwisefatal] [bit] NULL,
	[time_stamp] [timestamp] NOT NULL,
	[Qa_Rec_type] [int] NULL,
	[Lead_Disposition] [varchar](50) NULL,
 CONSTRAINT [PK__QA_Page___3213E83FA4B92350] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Parameter_Master]    Script Date: 25/06/2021 04:21:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Parameter_Master](
	[parameter_id] [int] IDENTITY(1,1) NOT NULL,
	[parameter_Name] [varchar](100) NULL,
	[Order_By] [int] NULL,
	[time_stamp] [timestamp] NOT NULL,
	[Font_Color] [varchar](50) NULL,
	[BackGround_Color] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[parameter_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Process_Logic]    Script Date: 25/06/2021 04:21:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Process_Logic](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Pid] [varchar](10) NULL,
	[QA_Disposition] [varchar](50) NULL,
	[Max_Attemp] [varchar](50) NULL,
	[Max_Count] [varchar](50) NULL,
	[Disposition] [varchar](50) NULL,
	[SubDisposition] [varchar](50) NULL,
	[TL_Disposition] [varchar](50) NULL,
	[Created_Date] [datetime] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[QA_Setup_Master]    Script Date: 25/06/2021 04:21:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QA_Setup_Master](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[recording_type] [varchar](100) NULL,
	[disposition] [varchar](100) NULL,
	[is_show_tl_dash] [bit] NULL,
	[is_show_tl_header] [bit] NULL,
	[show_report] [varchar](200) NULL,
	[is_showreport_weekwise] [bit] NULL,
	[upload_recording] [bit] NULL,
	[rating_selection] [varchar](5) NULL,
	[is_showlogic] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QA_Agent_Master] ADD  DEFAULT (getdate()) FOR [Created_Date]
GO

ALTER TABLE [dbo].[QA_Process_Logic] ADD  CONSTRAINT [DF__QA_Proces__Creat__3BE24DB9]  DEFAULT (getdate()) FOR [Created_Date]
GO






alter table QA_AgentDetails add FTR varchar(30)
alter table QA_AgentDetails add Call_Class varchar(50)
alter table QA_AgentDetails add Verifier_Code varchar(50)
alter table QA_AgentDetails add TL_Remarks varchar(500)
alter table QA_AgentDetails add Recoeding_path varchar(500)
alter table QA_AgentDetails add ShowInTLDashboard bit
alter table QA_AgentDetails add Recoeding_path varchar(500)
alter table QA_AgentDetails add WrongDisposition bit

alter table QA_Parameter_Master add Font_Color varchar(50)
alter table QA_Parameter_Master add BackGround_Color varchar(50)

alter table QA_Page_Master1 add CallStatus varchar(100)
Alter table QA_Page_Master1 ADD QA_Start_Date date
Alter table QA_Page_Master1 ADD QAProcess_ID int
alter table QA_Page_Master1 add SubCallStatus varchar(2000)
alter table QA_Page_Master1 add Qa_Rec_type int

alter table process_1_Log add status varchar(50)
alter table process_1_Log add QA_Assign_Agent varchar(50)
alter table process_1_Log add QA_Disposition varchar(50)
alter table process_1_Log add QA_Assign_TLAgent varchar(50)


alter table QA_MonitoringFormDetails add WrongDisposition varchar(10);
alter table QA_AgentDetails add ShowInTLDashboard bit,Recoeding_path nvarchar(300),FeedbackDone bit, WrongDisposition int;


alter table QA_Page_Master1 add Lead_Disposition varchar(50);
alter table QA_Page_Master1 add Qa_Rec_type int;




select ,pl.Crm_unique_Id,psl.* from Process_4_Log pl
left join Process_4_SubLog psl with(Nolock) on pl.Crm_unique_Id=psl.crm_unique_id 

 where Master_Id='21540825'
order by Log_id desc
-- Log_Id='41181015' 
select * from Process_4_Log where Log_id=41181015
--select * from Admin_Agent_Master where Agent_Name in ('SIPLIND19821','SIPLIND18915','SIPLIND18170')

 --Select Qa_Rec_type from QA_Page_Master1 where process_id='4'


 select pl.master_id,Convert(int,Sum(((Durn-Convert(int,Durn))*100)+(Convert(int,Durn)*60))) Durn 
from Process_4_log pl with(Nolock) 
left join Process_4_SubLog psl with(Nolock) on pl.Crm_unique_Id=psl.crm_unique_id 
 
where 1=1 and Master_id=21540825
group by pl.Master_id 






-----------------------------------------------------------------------------------



/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard]    Script Date: 25/06/2021 04:24:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- GET_QA_DASHBOARD 'Dev'
CREATE PROC [dbo].[get_QA_Dashboard]
@AGENT_NAME VARCHAR(100)
AS BEGIN
DECLARE @SQLQUERY VARCHAR(MAX),@URL VARCHAR(300)
SELECT @URL=WAVE_ADDRESS FROM COMPANY_SETUP
SET  @SQLQUERY='SELECT TOP 10 Q_ID,Log_ID,AGENT_NAME,AUDITOR_NAME,CUSTOMER_NAME,DATE_OF_AUDIT,DATE_OF_CALL,EXTERNAL_PERFORMANCE,INTERNAL_PERFORMANCE,FATAL_ERROR_CALL,
				CALL_SUMMARY,AREAS_OF_STRENGTH,AREAS_OF_DEVELOPMENT,'''+@URL+'''+RecordingUrl [RecordingUrl],isnull(FeedbackDone,''0'')FeedbackDone FROM QA_AGENTDETAILS
			    WHERE AGENT_NAME='''+@AGENT_NAME+''' and Auditor_Name!=Tlname  ORDER BY DATE_OF_AUDIT DESC ;'

	EXEC(@SQLQUERY)

	END



GO

/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard_Month]    Script Date: 25/06/2021 04:24:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--[get_QA_Dashboard_week] 'Dev'
CREATE Proc [dbo].[get_QA_Dashboard_Month]
@Agent_name varchar(100)
As begin
declare @Sqlquery varchar(max)
set @Sqlquery='select  Count(*)NoAuditer,avg(Internal_Performance)AVGSCORE ,Sum(case when Fatal_Error_Call=1  then 1 else 0  end )FatalErrorCount from QA_AGENTDETAILS 
where datepart(Month,Date_of_Audit) =datepart(MONTH,getdate()) and datepart(YEAR,Date_of_Audit) =datepart(YEAR,getdate()) and  Agent_Name ='''+@Agent_name+''' group by Agent_Name' 

exec(@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard_week]    Script Date: 25/06/2021 04:24:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- get_QA_Dashboard_week 'test3'
CREATE Proc [dbo].[get_QA_Dashboard_week]
@Agent_name varchar(100)
As begin
declare @Sqlquery varchar(max)
set @Sqlquery='select case when DAY(Date_of_Audit) between 1 and 7 then ''Week 1''
when DAY(Date_of_Audit) between 8 and 16 then ''Week 2''
when DAY(Date_of_Audit) between 16 and 23 then ''Week 3''
else ''Week 4'' end [Week], Count(*)NoAuditer,avg(Internal_Performance)AVGSCORE ,Sum(case when Fatal_Error_Call=1  then 1 else 0  end )FatalErrorCount from QA_AGENTDETAILS 
where datepart(Month,Date_of_Audit) =datepart(Month,getdate()) and datepart(YEAR,Date_of_Audit) =datepart(YEAR,getdate()) and  Agent_Name ='''+@Agent_name+''' group by Agent_Name , case when DAY(Date_of_Audit) between 1 and 7 then ''Week 1''
when DAY(Date_of_Audit) between 8 and 16 then ''Week 2''
when DAY(Date_of_Audit) between 16 and 23 then ''Week 3''
else ''Week 4'' end' 

exec(@Sqlquery)
print(@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[Get_QalityApproveData]    Script Date: 25/06/2021 04:24:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create Proc [dbo].[Get_QalityApproveData] 

@Pid varchar(10)

As begin



Declare @Sqlquery varchar(max)


set @Sqlquery='	select Reff_No[Ref ID],Application_No[APP],Campaign_Ids[CAMPAIGN_ID],TITLE,FORENAME,SURNAME,SEX,Process_'+@Pid+'_Master.Phone_No [MOBILE_TEL],Convert(varchar(10),End_Time,121)[CALL DATE],
					   PACCHOLD,Plan_Value[Plan],CallStatus[Dispostion],Agent_Name[Agent Name],EMI_OFFERED[EMI OFFERED],Status,Getdate()[Sent For Capture],EMI_Tenure[Tenure(In)],''''[Final Status],Remarks[Remark],log_id

				from Process_'+@Pid+'_Master 
				join Process_'+@Pid+'_Log on Process_'+@Pid+'_log.master_id=Process_'+@Pid+'_master.master_id
				where Qa_Disposition=''Quality Approve'' and isnull(FLag,0)=0  '

exec(@Sqlquery)


end 

GO

/****** Object:  StoredProcedure [dbo].[Get_QalityApproveDataAllRecording]    Script Date: 25/06/2021 04:24:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create Proc [dbo].[Get_QalityApproveDataAllRecording] 

@Pid varchar(10)

As begin



Declare @Sqlquery varchar(max)

set @Sqlquery='	select Process_'+@Pid+'_SubLog.Crm_unique_Id,Process_'+@Pid+'_SubLog.Unique_Id,Process_'+@Pid+'_SubLog.Rec_file 

				from Process_'+@Pid+'_log 

				join (select Master_id from Process_'+@Pid+'_log where Qa_Disposition=''Quality Approve'')QAlog on Process_'+@Pid+'_log.Master_id=QAlog.Master_Id

				join Process_'+@Pid+'_SubLog on Process_'+@Pid+'_log.Crm_unique_Id=Process_'+@Pid+'_SubLog.crm_unique_id

				where isnull(Process_'+@Pid+'_SubLog.Flag,0)=0  '

exec(@Sqlquery)



end 

GO



/****** Object:  StoredProcedure [dbo].[QA_AGENT_WISE_Report]    Script Date: 25/06/2021 04:30:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--Exec QA_AGENT_WISE_report '0','0','0','20200101','20200401','AgentWise','1','1'
CREATE proc [dbo].[QA_AGENT_WISE_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='IF  OBJECT_ID(N''tempdb..#temp'') IS NOT NULL
DROP TABLE #temp
select ROW_NUMBER() OVER (Order by QAA.Agent_Name)SrNo,
	QAA.Agent_Name,
	Emp_Code,
	Avg(External_Performance)[CallScore],
	CASE WHEN Avg(External_Performance)>=91 THEN ''A'' WHEN Avg(External_Performance)>=86 
	and Avg(External_Performance)<=90 THEN ''B'' ELSE ''C'' END [Category] 
	INTO #temp
	from QA_AgentDetails QAA join Admin_Agent_Master AAM ON QAA.Agent_Name=AAM.Agent_Name where 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND QAA.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
		SET @sql+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
		set @SQL+='group by QAA.Agent_Name,Emp_Code;

  SELECT SrNo,Agent_Name,Emp_Code,CallScore from #temp
	
	DECLARE @TotalAgent INT=0
	SELECT @TotalAgent=COUNT(1) FROM #temp;
	
	SELECT ''1''SrNo,''A (Good)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END),0) Number_of_Agents,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=95
	UNION ALL
	SELECT ''2'',''B (Average)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=91 and CallScore<=94
	UNION ALL
	SELECT ''3'',''C (Moderate)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore<90;'
	
	EXEC(@SQL)
	PRINT(@SQL)

END
GO

/****** Object:  StoredProcedure [dbo].[QA_AGENT_WISE_Report_WeekWise]    Script Date: 25/06/2021 04:30:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- Exec QA_AGENT_WISE_Report_WeekWise '0','0','0','0','AgentWise','4','1'
CREATE proc [dbo].[QA_AGENT_WISE_Report_WeekWise]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @month VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='IF  OBJECT_ID(N''tempdb..#temp'') IS NOT NULL
DROP TABLE #temp
select ROW_NUMBER() OVER (Order by QAA.Agent_Name)SrNo,
	QAA.Agent_Name,
	Emp_Code,
	Avg(External_Performance)[CallScore],
	CASE WHEN Avg(External_Performance)>=91 THEN ''A'' WHEN Avg(External_Performance)>=86 
	and Avg(External_Performance)<=90 THEN ''B'' ELSE ''C'' END [Category] ,(CASE 
	WHEN day(Date_of_Audit) between 1 and 7 THEN ''week1%'' 
    WHEN day(Date_of_Audit) between 8 and 15 THEN ''week2%'' 
    WHEN day(Date_of_Audit) between 15 and 23 THEN ''week3%''
    WHEN day(Date_of_Audit)>= 24 THEN ''week4%'' end) Weeks
	INTO #temp
	from QA_AgentDetails QAA join Admin_Agent_Master AAM ON QAA.Agent_Name=AAM.Agent_Name where 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND QAA.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
		SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;
		set @SQL+='group by QAA.Agent_Name,Emp_Code,day(Date_of_Audit);
		
	SELECT *
FROM
(
  SELECT SrNo,Agent_Name,Emp_Code,CallScore,weeks
  FROM #temp
) ss
PIVOT 
(
  sum(CallScore) FOR
  weeks IN ([week1%],[week2%],[week3%],[week4%])
) AS ff
	
	DECLARE @TotalAgent INT=0
	SELECT @TotalAgent=COUNT(1) FROM #temp;
	
	SELECT ''1''SrNo,''A (Good)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END),0) Number_of_Agents,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp 
	UNION ALL
	SELECT ''2'',''B (Average)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp
	UNION ALL
	SELECT ''3'',''C (Moderate)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp ;'
	
	EXEC(@SQL)
	PRINT(@SQL)

END

GO

/****** Object:  StoredProcedure [dbo].[QA_AgentDashboard]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE Proc [dbo].[QA_AgentDashboard]
@Pid varchar(10),@AgentName varchar(70)
-- QA_AgentDashboard 4,'test3'

as begin 

	Declare @Sqlquery varchar(max),@QAFiled varchar(2000),@QACustDisposition varchar(500)
	select @QACustDisposition=coalesce(@QACustDisposition+',','') +''''+replace(CallStatus,',',''',''')+''''  from QA_Page_Master1 where Process_ID=@Pid

		select @QAFiled=COalesce(@QAFiled+',','')+'PM1.'+Field_Name+QUOTENAME(Field_Description) from Process_Master where Pid=@Pid and Show_In_TMS='1'
		select @QAFiled=COalesce(@QAFiled+',','')+'Pl1.'+Field_Name+QUOTENAME(Field_Description) from Process_log where Pid=@Pid and Send_By_EMail='1'
		--select @QACustDisposition
		  set @Sqlquery='select /*pm1.Master_Id,Pl1.Log_id*/pl1.Srno,pm1.Phone_no,'+@QAFiled+' 
		 from Process_'+@Pid+'_Master PM1 join (select max(Log_id)Lastlog_id,master_id from Process_'+@pid+'_log 
		 where CallStatus in('+@QACustDisposition+') and  QA_Disposition in(''TL CALL'')  
		 group by master_id)LPL on LPL.master_id=PM1.master_id
		 Join Process_'+@Pid+'_Log PL1 on LPL.Lastlog_id=Pl1.Log_id where isnull(QA_Assign_TLAgent,'''')='''+@AgentName+'''  '
		exec (@Sqlquery)
		print (@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[QA_AuditWISE_REPORT]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--QA_AuditWISE_REPORT '0','0','0','','','0','4','0'

CREATE PROC [dbo].[QA_AuditWISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';
declare @LeadCallStatus varchar(50);
select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

SET @sql='select convert(varchar,ql.Assign_date,103)Assign_date, ql.Assign_To[QA Name],
count(Assign_To)[Total Lead Assign],
count(qa.Disposition)[Total leads audited],
(count(Assign_To)-count(qa.Disposition))[Audit pending],
sum(case when qa.Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when qa.Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(case when qa.Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from QA_AgentDetails qa 
right join QA_Assign_Log ql on ql.Log_Id=qa.Log_Id
where ql.Process_id='''+@Process_id+'''  and isnull(ql.Assign_To,'''')!='''' /*and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)*/ '
    IF(@tlid!='0')
    SET @sql+=' and qa.TLname='''+@tlid+'''';
    IF(@agentid!='0')
    SET @sql+=' and qa.Agent_Name='''+@agentid+'''';
    IF(@qaid!='0')
	SET @sql+=' and ql.Assign_To='''+@qaid+'''';
    SET @sql+='';

    SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,ql.Assign_date)>=CONVERT(DATE,GETDATE()) ' ELSE ' AND CONVERT(DATE,ql.Assign_date)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,ql.Assign_date)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,ql.Assign_date)<='''+@to+''' ' END;

SET @sql+=' group by ql.Assign_To,convert(varchar,ql.Assign_date,103) order by convert(varchar,ql.Assign_date,103),ql.Assign_To';
EXECUTE sp_executesql @SQL
Print(@SQL)

END



GO

/****** Object:  StoredProcedure [dbo].[QA_column_add]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- QA_column_add 1

CREATE proc [dbo].[QA_column_add] @pid varchar(10)
as begin

declare @sql varchar(max)='';

set @sql+='IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N''status''
          AND Object_ID = Object_ID(N''dbo.Process_'+@pid+'_Log''))
	BEGIN
    Alter Table Process_'+@pid+'_log add status varchar(50)
	END '

	set @sql+='IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N''QA_Assign_Agent''
          AND Object_ID = Object_ID(N''dbo.Process_'+@pid+'_Log''))
	BEGIN
    Alter Table Process_'+@pid+'_log add QA_Assign_Agent varchar(50)
	END '

	set @sql+='IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N''QA_Disposition''
          AND Object_ID = Object_ID(N''dbo.Process_'+@pid+'_Log''))
	BEGIN
    Alter Table Process_'+@pid+'_log add QA_Disposition varchar(50)
	END '

	set @sql+='IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N''TL_Status''
          AND Object_ID = Object_ID(N''dbo.Process_'+@pid+'_Log''))
	BEGIN
    Alter Table Process_'+@pid+'_log add TL_Status varchar(50)
	END '

	set @sql+='IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N''QA_Assign_TLAgent''
          AND Object_ID = Object_ID(N''dbo.Process_'+@pid+'_Log''))
	BEGIN
    Alter Table Process_'+@pid+'_log add QA_Assign_TLAgent varchar(50)
	END '

	exec(@sql)
--  print(@sql)
end
GO

/****** Object:  StoredProcedure [dbo].[QA_COUNT]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[QA_COUNT] @Pid varchar(10),@FromDate varchar(19)='',@ToDate varchar(19)='',@User_Type varchar(30),@QAAgent varchar(70)='',@Suserid varchar(40)='',@Agent varchar(30)=''

--QA_COUNT '1','','','Quality Manager','',''
--QA_COUNT '1','2019-11-01','','Quality Manager','',''
--QA_COUNT @Pid='1',@FromDate='',@ToDate='',@User_Type='',@QAAgent='',@Suserid=''
--QA_COUNT @Pid='1',@User_Type='M'
-- QA_COUNT '1','','','Quality TL','','dev'
--QA_COUNT '1','2019-11-07','','TL','','SUmit'
--QA_COUNT  @Pid=1,@FromDate='2019-11-07',@ToDate='2019-11-08',@User_Type='T',@QAAgent='--select--'
--QA_COUNT '1','','','A','','dev'
As begin



Declare @Sqlquery1 varchar(max),@Sqlquery2 varchar(max),@CusDispotion varchar(max),@Sqlquery3 varchar(max),@DataCon varchar(max),@DataCon1 varchar(max),@SqlCon varchar(max),@StartDateFrom varchar(19)--='2019-11-09'
	select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''',@StartDateFrom=Convert(varchar(10),Qa_Start_Date ,121) from QA_Page_Master1 where Process_id=@Pid
	set @DataCon=' and process_' + @Pid + '_log.Start_time>='''+@StartDateFrom+''' '+case when @FromDate='' then '  and  process_' + @Pid + '_log.Start_time>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and process_' + @Pid + '_log.Start_time>='''+@FromDate+''''  end+ case when @ToDate='' then ' and process_' + @Pid + '_log.Start_time<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and process_' + @Pid + '_log.Start_time<='''+@ToDate+''''  end 
	set @DataCon1= ' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom+''''  +case when @FromDate='' then ' and QA_Assign_Log.Assign_Date >='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_Assign_Log.Assign_Date>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_Assign_Log.Assign_Date<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_Assign_Log.Assign_Date<='''+@ToDate+''''  end 

	set @SqlCon=' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom+''' '+ case when @FromDate='' then ' and  QA_AgentDetails.Date_of_Audit>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_AgentDetails.Date_of_Audit>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_AgentDetails.Date_of_Audit<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_AgentDetails.Date_of_Audit<='''+@ToDate+''''  end 
	
if(@Agent not in ('','--Select--'))
set @SqlCon=' and process_' + @Pid + '_log.Agent_name = '''+@Agent+''''
	--select @CusDispotion,@StartDateFrom
	if @User_Type='Quality Manager'
	begin
	
	set @Sqlquery1='select count(1) [Total] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where  CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +
	case when @Agent='' or @Agent='--select--'  then ' ' else ' and process_' + @Pid + '_log.Agent_name='''+@Agent+'''' end +' ;

					select count(1) [Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id where CallStatus in('+@CusDispotion+')  AND process_' + @Pid + '_log.QA_Disposition is null and Start_time>='''+@StartDateFrom +''''+ case when @QAAgent='' or @QAAgent='--select--'  then ' and status is null ' else ' and status=''1'' and QA_Assign_Agent='''+@QAAgent+'''' end +
					case when @Agent='' or @Agent='--select--'  then ' ' else ' and process_' + @Pid + '_log.Agent_name='''+@Agent+'''' end +' ;

	                select count(1) [Quality Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Rework] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Rework''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
	                select count(1) [Quality Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
				    select count(1) [Quality Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where   QA_Disposition Is Null and CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else '  and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Call] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Call''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Rework Done] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Rework Done''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
	  
	   -- exec  (@Sqlquery1)      
	end

else if @User_Type='Quality TL'
	begin
	Declare @TlId varchar(20)
	select @TlId=User_Id from User_Master where User_Name=@Suserid;
	 set @Sqlquery1='select count(1) [Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+')  and (status is null or status=1) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+'''' + @SqlCon+';
					 select count(1) [Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+') and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom +''';
					 select count(1) [Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Rework'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Rework'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+ @TlId +'''' + @SqlCon+';
					 select count(1) [Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Hold'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on  process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Call] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on  process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Call'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [Rework Done] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Rework Done'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Reject'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';'
		--exec(@Sqlquery1)
	end
if @User_Type='Quality Agent'
	begin
	
	set @Sqlquery1='select count(1)[Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+')  and status=1 and QA_Assign_Agent='''+@Suserid+''''+ @DataCon1+';
					select count(1)[Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+') and  (process_'+@Pid+'_log.QA_Disposition is null ) and QA_Assign_Agent='''+@Suserid+''' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom +''' ;
					select count(1)[Quality Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Approve''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id    join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Rework''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid    Left Join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Hold''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id     join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid     Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Reject'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+'; 
					select count(1)[TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		      join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[TL Call] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Call''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[Rework Done] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Rework Done''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
          

		 --select (@Sqlquery3)
	  --exec (@Sqlquery1)      
	end
	exec(@Sqlquery1)
	print(@Sqlquery1)
end



GO

/****** Object:  StoredProcedure [dbo].[QA_Defect_Analysis_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






--QA_Defect_Analysis_Report '0','0','0','0','0','0','4','0'
CREATE PROC [dbo].[QA_Defect_Analysis_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='SELECT ROW_NUMBER() OVER (ORDER BY SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END) desc)RN,Question,SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END) [Defect_Count]
			  /*,SUM (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END))desc)[Cumulative_Frequency]
			  ,(SUM (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END))desc)*100.00)/nullif(SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END),0)[per_Cumulative_Frequency]*/
			  into #temp
			  from QA_MonitoringFormDetails QMFD join QA_AgentDetails QAA ON QMFD.Q_id=QAA.Q_id where Rating=''No'' and 1=1'
			  IF(@tlid!='0')
				SET @sql+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @sql+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
			  SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
			  SET @sql+=CASE WHEN @from='' THEN ' AND 1=1 ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			  set @SQL+='group by Question
						 order by (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) desc
	
	DECLARE @TOTAL int
	select @TOTAL=SUM(Defect_Count) from #temp
	
	select RN SrNo,Question,[Defect_Count],SUM (SUM([Defect_Count])) OVER (ORDER BY RN)[Cumulative_Frequency],
	(SUM (SUM([Defect_Count])) OVER (ORDER BY RN)*100)/@TOTAL [per_Cumulative_Frequency] from #temp
	group by RN,Question,[Defect_Count]'
	
	EXEC(@SQL)

END



GO

/****** Object:  StoredProcedure [dbo].[QA_DQR_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- QA_DQR_Report '','','','2021-05-13','','','4',''

CREATE PROC [dbo].[QA_DQR_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)

AS
BEGIN

Declare @Question nvarchar(max),@sql nvarchar(max),@Question_Name nvarchar(max),@Master_Field Varchar(max),@Log_Field Varchar(max),@ffrom varchar(20),@fto varchar(20),
@HideNumber bit=0,@HideField bit=1;
set @ffrom=@from;
set @fto=@to;

set @from=case when @from='' then COnvert(varchar(10),getdate(),121) else @from end ;
set @to=case when @to='' then COnvert(varchar(10),getdate()+1,121) else @to end ;
selEct @HideNumber=Hide_Phone_No from Process where pid=@Process_id
Select @Question= COALESCE (@Question + ',','') + 'MAX(CASE WHEN Question='''+QUOTENAME(Convert(varchar(300),Question))+''' THEN Rating END) ' +QUOTENAME(Convert(varchar(300),Question))
+ ',MAX(CASE WHEN Question='''+QUOTENAME(Convert(varchar(300),Question))+''' THEN Convert(varchar(max),QA_comment) else NULL END) ' +QUOTENAME(Convert(varchar(300),Question+'_QA_comment'))
from QA_Page_Master where process_id=@Process_id  order by id ASC;
select @Question_Name=COALESCE(@Question_Name + ',','')+QUOTENAME(Convert(varchar(128),Question))+','+QUOTENAME(Convert(varchar(300),Question+'_QA_comment')) from QA_Page_Master where process_id=@Process_id  order by id ASC;
Select @Master_Field= COALESCE (@Master_Field + ',','') + 'Pm.'+QUOTENAME(Field_Name) from Process_master where pid=@Process_id and Show_In_TMS=1 order by Order_No;
Select @Log_Field= COALESCE (@Log_Field + ',','') + 'Pl.'+QUOTENAME(Field_Name) from Process_log where pid=@Process_id and Send_By_EMail=1 order by Order_No;

-- QA_DQR_Report '','','','2021-05-13','','','4',''
--select @Master_Field
set @sql='
select Q_id,'+@Question+' into #temp from QA_MonitoringFormDetails group by  Q_id 

select QAD.Log_id,pm.Master_Id'+iif(@HideField=2,iif(@HideField=1,',''XXXXXX''+right(pm.phone_no,4) Phone_no',',pm.Phone_no'),'')+',QAD.Agent_Name,CallerEMPID [Agent_Code],
TLname [Team_Leader_Name],Date_of_Call [Call_date],Auditor_Name,Date_of_Audit,
'+isnull(@Master_Field,'')+','+isnull(@Log_Field,'')+',Date_of_Audit [Verifier_Date], Disposition [Call_Status],'+@Question_Name+',FTR FTR_Status
,External_performance[Score_without_Fatal %],Internal_Performance[Score_with_Fatal %],QAD.call_summary,QAD.areas_of_strength,QAD.areas_of_development,
QAD.TL_Remarks,case when FeedbackDone=''1'' then ''Accept'' when FeedbackDone=''2'' then ''Deny'' else '''' end FeedbackDone
from QA_AgentDetails QAD
left join Process_'+@Process_id+'_log pl on QAD.log_id=pl.Log_Id
left join Process_'+@Process_id+'_Master PM on pl.Master_Id=pm.Master_Id
left join #temp QMFD on qmfd.Q_id=qad.Q_id
where convert(date,QAD.Date_of_Audit)>='''+@from+''' and convert(date,QAD.Date_of_Audit)<='''+@to+''' '+case when isnull(@tlid,'')!='' and @tlid!='0' then ' and QAD.TLname='''+@tlid+''' ' else '' end 
+case when isnull(@agentid,'')!=''and @agentid!='0' then ' and QAD.Agent_Name='''+@agentid+''' ' else '' end 
+case when isnull(@qaid,'')!=''and @qaid!='0' then ' and QAD.Auditor_Name='''+@qaid+''' ' else '' end +' 
order by QAD.Date_of_Audit asc'
exec (@sql)
print(@to)
if  object_id('SBI_Flexi.dbo.traceDQR') is null
begin
Create table traceDQR(tlid VARCHAR(30), agentid VARCHAR(30), qaid VARCHAR(30), Qfrom VARCHAR(10), Qto VARCHAR(10), reporttype VARCHAR(30),Process_id VARCHAR(10),UserId VARCHAR(10),Qsql varchar(max))

end
Insert into traceDQR(tlid, agentid, qaid, Qfrom , Qto, reporttype,Process_id,UserId,Qsql) values (@tlid, @agentid, @qaid, @ffrom, @fto, @reporttype,@Process_id,@UserId,@sql);

END



GO

/****** Object:  StoredProcedure [dbo].[QA_FTR_Status_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[QA_FTR_Status_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)


-- QA_FTR_Status_Report '','','','2021-05-10','','','4',''
AS BEGIN

	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

    DECLARE @SQL varchar(MAX)
    set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,Emp_Code,
              SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END)[Total Sale],SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)[FTR_Status],
              (SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)*100)/nullif(SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END),0) FTR_Status_per
              into #temp1 from QA_AgentDetails qa right join Process_'+@Process_id+'_log pl on qa.log_id=pl.Log_Id
              left join Admin_Agent_Master on Admin_Agent_Master.Agent_name=pl.agent_Name where 1=1'
        IF(@tlid!='0')
        SET @sql+=' AND TLname='''+@tlid+''' ';
        IF(@agentid!='0')
        SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
        IF(@qaid!='0')
        SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
        SET @sql+=CASE WHEN @from='' THEN ' AND 1=1 ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
        SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
    set @SQL=@SQL+' group by pl.Agent_Name,Emp_Code;

    select SrNo,Agent_Name,Emp_Code,[Total Sale],[FTR_Status],FTR_Status_per from #temp1
    UNION ALL
    select NULL,''Grand Total'','''',SUM([Total Sale]),SUM([FTR_Status]),(SUM(FTR_Status)*100)/nullif(SUM([Total Sale]),0) from #temp1 '

    set @SQL+='select ROW_NUMBER() OVER (Order by Case WHEN GROUPING(TlName)=1 THEN 1 Else 0 End)SrNo,Case WHEN GROUPING(TlName)=1 THEN ''Grand Total'' Else TlName End TL_Name,
                SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END)[Total Sale],SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)[FTR_Status],
                (SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)*100)/nullif(SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END),0) FTR_Status_per
               from QA_AgentDetails qa right join Process_'+@Process_id+'_log pl on qa.log_id=pl.Log_Id where 1=1 '
        IF(@tlid!='0')
        SET @sql+=' AND TLname='''+@tlid+''' ';
        IF(@agentid!='0')
        SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
        IF(@qaid!='0')
        SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
        SET @sql+=CASE WHEN @from='' THEN ' AND 1=1 ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
        SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
    set @SQL=@SQL+'group by TLname with Cube;'

    set @SQL+='SELECT ROW_NUMBER() OVER (Order by TLname)SrNo,TLname TL_Name,AVG(External_Performance) Average_of_Call_Score_per into #temp from QA_AgentDetails WHERE 1=1 '
    IF(@tlid!='0')
        SET @sql+=' AND TLname='''+@tlid+''' ';
        IF(@agentid!='0')
        SET @sql+=' AND Agent_Name='''+@agentid+''' ';
        IF(@qaid!='0')
        SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
        SET @sql+=CASE WHEN @from='' THEN ' AND 1=1 ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
        SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
    set @SQL=@SQL+'group by TLname;

    SELECT SrNo,TL_Name,Average_of_Call_Score_per from #temp
    UNION ALL
    SELECT NULL,''AVG_Total'',SUM(Average_of_Call_Score_per)/nullif(COUNT(*),0) from #temp ;'

    EXEC(@SQL)
    PRINT(@SQL)
END 
GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetails]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROC [dbo].[QA_LogDetails]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@LogID varchar(10)

AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select Q_id,P_id,Agent_Name [Agent Name],Auditor_Name [Auditor Name],Customer_Name [Customer Name],Date_of_Call [Date of Call],Date_of_Audit [Date of Audit],Mobile [Mobile],Brand [Brand],Month,AHT,External_Performance [External Performance],Internal_Performance [Internal Performance],Fatal_Error_Call [Fatal Error Call],call_summary [Call Summary],areas_of_strength [Areas of strength],areas_of_development [Areas OfD evelopment],RecordingUrl [Recording Url],CallerEMPID [Caller EMPID],TLname [TL Name],Disposition [Disposition],ManagerName [Manager Name],LeadID [Lead ID],LeadStatusTime [Lead Status Time],Audit,CallDuration [Call Duration],Week,LeadSource [Lead Source],EvaluationMode [Evaluation Mode],ACPT,Log_Id,time_stamp from QA_AgentDetails where Log_Id = '+@LogID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetails] '43692'


GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetailsWithQ_ID]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROC [dbo].[QA_LogDetailsWithQ_ID]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@QID varchar(10)
--@LogID varchar(10)
AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select 
Parameter_Name [Parameter Name],status,Question,Weightage,Applicable_Weightage [Applicable Weightage],Agents_Grade[Agents Grade],Rating,QA_comment[QA comment] from QA_MonitoringFormDetails  where Q_ID = '+@QID+'
select 
call_summary from QA_AgentDetails  where Q_ID = '+@QID+'
select 
areas_of_strength from QA_AgentDetails  where Q_ID = '+@QID+'
select 
areas_of_development from QA_AgentDetails  where Q_ID = '+@QID+'
select 
TL_Remarks from QA_AgentDetails  where Q_ID = '+@QID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetailsWithQ_ID] '43692'



GO

/****** Object:  StoredProcedure [dbo].[QA_Manger]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_Manger]

--QA_Manger '0','100','4','','','','','','',''
--QA_Manger'0','100','1','','','--select--','','--select--','--select--',''
--QA_Manger'0','100','4','2019-12-28','','--select--','','--select--','--select--',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@Agent_name varchar(70)='',@Type varchar(30),@Agent varchar(30)='',
@Disposition varchar(80)='',@SubDisposition varchar(80)=''

AS BEGIN
DECLARE @sql varchar(max) = '';
DECLARE @CusDispotion varchar(max),@StartDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100);

select @StartDateFrom=Convert(varchar(10),Qa_Start_Date ,121) from QA_Page_Master1 where Process_id=@Pid


if (@Agent_name= '--select--')
set @Agent_name=''


select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0 and
Field_Type not in ('I-Frame')

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end

set @datefield=case when  @Type='NOT ASSIGN' THEN ' ' WHEN @Type in('TOTAL','','Quality Pending') THEN ' Start_time >='''+@StartDateFrom+''' and convert(date,Start_Time)' ELSE ' Date_of_Audit >='''+@StartDateFrom+''' and convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid
print(@List)
    set @sql +='select Q_id,Calllog.Log_id ,MasterData.Master_ID,Calllog.Log_id[Log Id],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],QA_AgentDetails.Disposition,QA_Assign_Agent [QA Name],'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData
    left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
    left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
     where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)   '

    if @Type='NOT ASSIGN'
         set @sql+='and Start_time>='''+@StartDateFrom +'''';
     if @Type not in('NOT ASSIGN','total','','Quality Pending')
         set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '
     if(@Type in('Quality Pending') )
     set @sql+='and (Calllog.QA_Disposition is null )';

     if(@Type in('')) AND (@Agent_name!='')
     set @sql+='and (Calllog.QA_Disposition is null )';
     else if(@Type='') AND (@Agent_name='')
     set @sql+='and (Calllog.QA_Disposition is null and Calllog.Status is null )';

     if(@datefield!='')
     set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

     else
     set @sql+=' and Calllog.QA_Disposition is null   '

      If (@Agent_name not in ('','--select--'))
        set @sql+= ' and QA_Assign_Agent ='''+@Agent_name+''' '
     if(@Type='NOT ASSIGN' )
        Set @sql+=' and Calllog.status is null '


        --By Chetan on 31Aug2020
        if(@Agent not in ('','--select--'))
            Set @sql+=' and Calllog.Agent_Name ='''+@Agent + ''' '
		if(@Disposition not in ('','--select--'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '
			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '

			
 set @sql+='   ; Select * from #tempp order by Log_id
    offset '+@pageindex+' rows fetch next '+@pagesize+' row only
    select count(*) from #tempp '
    print(@sql)
    exec  (@sql)

END


--QA_Manger '0','100','1','2018-10-10','','munish','NOT ASSIGN'
--QA_Manger'0','100','1','','','--select--',''
--QA_Manger'0','100','1','','','--select--',''
--QA_Manger_AgentWise'0','100','1','','','munish',''

GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_AgentWise]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE proc [dbo].[QA_Manger_AgentWise]

--QA_Manger_AgentWise '0','100','1','','','munish',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',
@user varchar(20),@Type varchar(30),@Disposition varchar(30)='',@SubDisposition varchar(30)=''

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(2000),@PandingDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(50);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--select 'MasterData.'+Field_Name[Field_Name] into #ActiveField from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0
--Union
--select 'Calllog.'+Field_Name[Field_Name] from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0

--DECLARE @List VARCHAR(8000),@datefield varchar(50); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,QA_Assign_Log.Assign_Date)' ELSE 'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end

	set @sql='select Q_id,Calllog.Log_id,MasterData.Master_Id,Calllog.Log_id[Log Id],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Campaign_master.Campaign_Name,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],Calllog.CallType,QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData 
	Join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
	Join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  Calllog.Log_id=LastAssign.Log_id 
	Join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	Left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 if @Type='NOT ASSIGN'
	 	set @sql+=' and QA_Assign_Log.Assign_Date>='''+@PandingDateFrom +''' ';
	 
	 if(@Type='')
	 set @sql+='and Calllog.QA_Disposition is null ';
	 if(@datefield!='')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 else
	 set @sql+=' and Calllog.QA_Disposition is null   '

	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '

	  If (@user  not in (''))
		set @sql+= ' and QA_Assign_Agent ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and status is null '

		if(@Disposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '

			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '


 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END
GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_TLWise]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_Manger_TLWise]


--QA_Manger_TLWise '0','100','4','','','20','','',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20)
,@Type varchar(30),@Disposition varchar(30),@SubDisposition varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(2000),@PandingDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100),@cond varchar(200);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--DECLARE @List VARCHAR(8000),@datefield varchar(50),@cond varchar(200); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,Start_Time)' ELSE 'convert(date,Date_of_Audit)' END
set @datefield='convert(date,Date_of_Audit)'
--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN  'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
set @cond=case when @Type='TOTAL'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' 
when @Type='PENDING'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'') and Admin_Agent_Master.Team_Leader_Id ='''+@user+''''
when @Type='QUALITY REWORK'  then  '  and Calllog.QA_Disposition = ''Quality Rework'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='QUALITY HOLD'  then  ' and Calllog.QA_Disposition = ''Quality Hold'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='TL APPROVE'  then  '  and Calllog.QA_Disposition = ''TL Approve'''
when @Type='TL Hold'  then  '  and Calllog.QA_Disposition = ''TL Hold'''
when @Type='TL Call'  then  '  and Calllog.QA_Disposition = ''TL Call'''
when @Type='TL Rework Done'  then  ' and Calllog.QA_Disposition = ''Rework Done'''
when @Type='TL REJECT'  then  ' and Calllog.QA_Disposition = ''TL Reject'''


else  ' and Calllog.QA_Disposition != '''' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' end

	set @sql='select Q_id,Calllog.Log_id,MasterData.Master_Id,Calllog.Log_id[Log ID],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Campaign_master.Campaign_Name,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],Calllog.CallType,QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],Convert(varchar,QA_AgentDetails.Date_of_Audit,121)[Date of Audit],'+@Pid+'[PID] into #tempp from process_'+@Pid+'_master MasterData 
	Join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
	Left join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  Calllog.Log_id=LastAssign.Log_id 
	Left join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	Left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  QA_AgentDetails.Date_of_Audit>='''+@PandingDateFrom +''' and Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 
	 if(@Type='')
	 set @sql+='';
	 if(@datefield!='' AND @Type!='PENDING')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 ----else
	 --set @sql+=' and QA_Disposition is null   '
--' and QA_Disposition = '''+@Type+''' 
	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=@cond

	 
	  If (@user  not in (''))
		set @sql+= ' and Admin_Agent_Master.Team_Leader_Id ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and (status is null or status = 1) '

		if(@Disposition not in ('','--select--'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '
			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '



 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END

GO

/****** Object:  StoredProcedure [dbo].[QA_MonitoringDetail]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--QA_MonitoringDetail '4','1673555'
CREATE PROC [dbo].[QA_MonitoringDetail]
@Pid varchar(10),@log_id varchar(70)

AS BEGIN
DECLARE @sql varchar(max),@List VARCHAR(max),@List1 VARCHAR(max);


select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
 --SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField; 
--select @List

	set @sql='select  '+@List+' into #tempp from process_'+@Pid+'_master MasterData 
	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	 where '
	 begin
	 set @sql+= 'log_id ='''+@log_id+''''
	 end
	set @sql+='Select * from #tempp'
	--offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	--select count(*) from #tempp 
	--print(@sql)
	exec  (@sql)
END


GO

/****** Object:  StoredProcedure [dbo].[QA_Monitringform]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_Monitringform] @QACondition varchar(20), @Pid varchar(10),@Log_id varchar(20),@Agent_name varchar(100)=NULL As begin

Declare @sqlquery Varchar(max)
if @QACondition='1'
begin

-- QA_Monitringform '1','1','6091',''

set @sqlquery='select pl.Agent_Name,convert(date,pl.Start_Time)[Start_Time],um.User_Name,Emp_Code,
Convert(varchar,psl.durn/3600)+'':''+(Convert(varchar,(psl.durn%3600)/60))+'':''+(Convert(varchar,(psl.durn%3600)%60))[durn] 
from process_'+@Pid+'_log pl 
left join Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name 
left join User_Master um on um.User_Id=aam.Team_Leader_Id 
left join (select pl.Crm_unique_Id,Convert(int,Sum(((Durn-Convert(int,Durn))*100)+(Convert(int,Durn)*60))) Durn 
from Process_'+@Pid+'_log pl with(Nolock) 
join Process_'+@Pid+'_SubLog psl with(Nolock) on pl.Crm_unique_Id=psl.crm_unique_id 
where 1=1 and Log_Id='''+@Log_id+'''  
group by pl.Crm_unique_Id )Psl on psl.Crm_unique_Id=pl.Crm_unique_Id 
where 1=1 and Log_Id='''+@Log_id+''' '

end
else if @QACondition='2'
begin

-- QA_Monitringform '2','1','6091','anuj'

set @sqlquery='select pl.Agent_Name,convert(date,pl.Start_Time)[Start_Time],um.User_Name,Emp_Code,
Convert(varchar,psl.durn/3600)+'':''+(Convert(varchar,(psl.durn%3600)/60))+'':''+(Convert(varchar,(psl.durn%3600)%60))[durn] 
from process_'+@Pid+'_log pl 
left join Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name 
left join User_Master um on um.User_Id=aam.Team_Leader_Id 
left join (select pl.Crm_unique_Id,Convert(int,Sum(((Durn-Convert(int,Durn))*100)+(Convert(int,Durn)*60))) Durn 
from Process_'+@Pid+'_log pl with(Nolock) 
join Process_'+@Pid+'_SubLog psl with(Nolock) on pl.Crm_unique_Id=psl.crm_unique_id 
where 1=1 and pl.Agent_Name='''+isnull(@Agent_name,'')+''' 
group by pl.Crm_unique_Id )Psl on psl.Crm_unique_Id=pl.Crm_unique_Id 
where 1=1 and Log_Id='''+@Log_id+''' and pl.Agent_Name='''+isnull(@Agent_name,'')+''' '

end

else if @QACondition='3'
begin

-- QA_Monitringform '3','1','6091',''

set @sqlquery='select lpl.Agent_Name,convert(date,pl.Start_Time)[Start_Time],um.User_Name,Emp_Code,
Convert(varchar,psl.durn/3600)+'':''+(Convert(varchar,(psl.durn%3600)/60))+'':''+(Convert(varchar,(psl.durn%3600)%60))[durn] 
from process_'+@Pid+'_log pl  
join (select top 1 Master_Id,agent_name from Process_'+@Pid+'_Log where Log_Id='''+@Log_id+'''  )lpl on lpl.Master_Id=pl.Master_Id
left join Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name 
left join User_Master um on um.User_Id=aam.Team_Leader_Id 
left join (select pl.Crm_unique_Id,Convert(int,Sum(((Durn-Convert(int,Durn))*100)+(Convert(int,Durn)*60))) Durn 
from Process_'+@Pid+'_log pl with(Nolock) join Process_'+@Pid+'_SubLog psl with(Nolock) on pl.Crm_unique_Id=psl.crm_unique_id 
where 1=1 and pl.Master_Id=(select top 1 Master_Id from Process_'+@Pid+'_Log where Log_Id='''+@Log_id+'''  ) 
group by pl.Crm_unique_Id )Psl on psl.Crm_unique_Id=pl.Crm_unique_Id 
where 1=1 order by pl.Log_Id'

end

exec(@sqlquery)




end
GO

/****** Object:  StoredProcedure [dbo].[QA_Performers_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[QA_Performers_Report]
@tlid VARCHAR(30)='', @agentid VARCHAR(30)='', @qaid VARCHAR(30)='', @from VARCHAR(10)='', @to VARCHAR(10)='', @reporttype VARCHAR(30)='',@Process_id VARCHAR(10)='',@UserId VARCHAR(10)=''

AS
BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='DECLARE @RANK table (SrNo int)
			  INSERT INTO @RANK(SrNo) VALUES (''1''),(''2''),(''3'')
	
			  DECLARE @Performers table (SrNo int,Agent_Name varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers (SrNo,Agent_Name,CallScore,[Week])
			  select top 3 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,Agent_Name,Avg(External_Performance)[CallScore],
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week] 
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @SQL+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  
			  SET @SQL+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			 
			  set @SQL=@SQL+' group by Agent_Name ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(Agent_Name,'''')Agent_Name,isnull(CallScore,''0'')CallScore from @RANK r  Left Join @Performers p on r.SrNo=p.SrNo;'
			  
			  
			  set @SQL+='DECLARE @RANK_TL table (SrNo int)
						INSERT INTO @RANK_TL(SrNo) VALUES (''1'')
			  
			  DECLARE @Performers_TL table (SrNo int,TLname varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers_TL (SrNo,TLname,CallScore,[Week])
			  select  top 1 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,TLname,Avg(External_Performance)[CallScore] ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week]
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @SQL+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  
			  SET @SQL+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			 
			  set @SQL=@SQL+' group by TLname ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(TLname,'''')TLname,isnull(CallScore,''0'')CallScore from @RANK_TL r  Left Join @Performers_TL p on r.SrNo=p.SrNo'

	EXEC(@SQL)
	print(@SQL)
	--exec QA_Performers_Report '0','0','0','20171231','','Performers','1','1'
END
GO

/****** Object:  StoredProcedure [dbo].[QA_Performers_Report_WeekWise]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--exec QA_Performers_Report_WeekWise '0','0','0','5','Performers','4','1'
CREATE PROC [dbo].[QA_Performers_Report_WeekWise]
@tlid VARCHAR(30)='', @agentid VARCHAR(30)='', @qaid VARCHAR(30)='',@month VARCHAR(10), @reporttype VARCHAR(30)='',@Process_id VARCHAR(10)='',@UserId VARCHAR(10)=''

AS
BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='DECLARE @RANK table (SrNo int);
			  INSERT INTO @RANK(SrNo) VALUES (''1''),(''2''),(''3'');
	DECLARE @week table (weekno int)
			  INSERT INTO @week(weekno) VALUES  (''1''),(''2''),(''3''),(''4'');

			  DECLARE @Performers table (SrNo int,Agent_Name varchar(50),CallScore int,[Week] varchar(5));

			  INSERT INTO @Performers (SrNo,Agent_Name,CallScore,[Week])
			  select  ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,Agent_Name,Avg(External_Performance)[CallScore],
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week] 
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;

			  set @SQL=@SQL+' group by Agent_Name ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  having Avg(External_Performance)>0 Order by Avg(External_Performance) desc;
			  
			  --Query 1
			  with cte as(
			select  dense_Rank() over(partition by p.week order by CallScore desc)RN1,
			p.[Week],isnull(Agent_Name,'''')Agent_Name,
			isnull(CallScore,''0'')CallScore 
			from  @Performers p )
			,cte1 as(select ROW_NUMBER()over(partitiOn by week order BY CallScore desc)srno,Week,Agent_Name,CallScore from cte where rn1=1  )
			select wk.weekno[Week],Isnull(cte1.Agent_Name,'''')Agent_Name,isnull(cte1.CallScore,''0'')CallScore from @week wk left join cte1 on wk.weekno=cte1.Week
			Where( srno Is null or (srno is not null anD srno<=3));
			
			 with cte as(
			select  dense_Rank() over(partition by p.week order by CallScore desc)RN1,
			p.[Week],isnull(Agent_Name,'''')Agent_Name,
			isnull(CallScore,''0'')CallScore 
			from  @Performers p )
			,cte1 as(select ROW_NUMBER()over(partitiOn by week order BY CallScore desc)srno,Week,Agent_Name,CallScore from cte where rn1=2  )
			select wk.weekno[Week],Isnull(cte1.Agent_Name,'''')Agent_Name,isnull(cte1.CallScore,''0'')CallScore from @week wk left join cte1 on wk.weekno=cte1.Week
			Where( srno Is null or (srno is not null anD srno<=3));
			
			 with cte as(
			select  dense_Rank() over(partition by p.week order by CallScore desc)RN1,
			p.[Week],isnull(Agent_Name,'''')Agent_Name,
			isnull(CallScore,''0'')CallScore 
			from  @Performers p )
			,cte1 as(select ROW_NUMBER()over(partitiOn by week order BY CallScore desc)srno,Week,Agent_Name,CallScore from cte where rn1=3  )
			select wk.weekno[Week],Isnull(cte1.Agent_Name,'''')Agent_Name,isnull(cte1.CallScore,''0'')CallScore from @week wk left join cte1 on wk.weekno=cte1.Week
			Where( srno Is null or (srno is not null anD srno<=3));'
			  
			  set @SQL+='DECLARE @RANK_TL table (SrNo int)
						INSERT INTO @RANK_TL(SrNo) VALUES (''1'')
			  
			  DECLARE @Performers_TL table (SrNo int,TLname varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers_TL (SrNo,TLname,CallScore,[Week])
			  select  top 1 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,TLname,Avg(External_Performance)[CallScore] ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week]
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;
			 
			  set @SQL=@SQL+' group by TLname ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  --Query 2
			   with cte as(
			  select isnull(r.SrNo,1)SrNo,ROW_NUMBER() OVER (partition By wk.weekno order by wk.weekno) weekwise,wk.weekno[Week],isnull(TLname,'''')TLname,
			  isnull(CallScore,''0'')CallScore 
			  from @week wk left join  @Performers_TL p on wk.weekno=p.Week left join @RANK_TL r on r.SrNo=p.SrNo)
			  select SrNo,Week,TLname,CallScore from cte where weekwise<=3
			  ;'
			  --select r.SrNo,[Week],isnull(TLname,'''')TLname,isnull(CallScore,''0'')CallScore from @RANK_TL r  Left Join @Performers_TL p on r.SrNo=p.SrNo'

	EXEC(@SQL)
	print(@SQL)
	
END

GO

/****** Object:  StoredProcedure [dbo].[QA_Sale_Wise_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--QA_Sale_Wise_Report '0','0','0','2019-12-01','2019-12-21','0','1','0'
CREATE PROC [dbo].[QA_Sale_Wise_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;



	set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,Emp_Code,
	SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
			  into #temp1 from Process_'+@Process_id+'_Log pl
			  LEFT JOIN Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name
			  left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
			  --Left Join User_Master UM on UM.User_Id=pl.Team_Leader_Id
			  where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND User_Name='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
		SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Start_time)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Start_time)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Start_time)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Start_time)<='''+@to+''' ' END;
	set @SQL+='group by pl.Agent_Name,Emp_Code;

	select SrNo,Agent_Name,Emp_Code,[Week 1],[Week 2],[Week 3],[Week 4],[Fatal Calls] from #temp1
	UNION ALL
	select NULL,''Total Sale Count'','''',SUM([Week 1]),SUM([Week 2]),SUM([Week 3]),SUM([Week 4]),sum([Fatal Calls]) from #temp1 order by 2'

	set @SQL+='select ROW_NUMBER() OVER (Order by CASE WHEN GROUPING(User_Name)=1 THEN 1 ELSE 0 END)SrNo,
			   CASE WHEN GROUPING(User_Name)=1 THEN ''Grand Total'' ELSE User_Name END [TL_Name],
			    SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
				from Process_'+@Process_id+'_Log pl left join User_Master on pl.Team_Leader_Id=User_Id left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
				where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
				IF(@tlid!='0')
				SET @sql+=' AND User_Name='''+@tlid+''' ';
				IF(@agentid!='0')
				SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
				IF(@qaid!='0')
				SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
				SET @sql+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Start_time)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Start_time)>='''+@from+''' ' END;
				SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Start_time)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Start_time)<='''+@to+''' ' END;
			set @SQL+='group by User_Name with cube'

	EXEC(@SQL)
	PRINT(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[QA_Sale_Wise_Report_WeekWise]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- QA_Sale_Wise_Report '0','0','0','2021-06-01','2021-06-15','0','4','0'

CREATE PROC [dbo].[QA_Sale_Wise_Report_WeekWise]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @month VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;



	set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,Emp_Code,
	SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
			  into #temp1 from Process_'+@Process_id+'_Log pl
			  LEFT JOIN Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name
			  left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
			  --Left Join User_Master UM on UM.User_Id=pl.Team_Leader_Id
			  where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND User_Name='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
		SET @sql+=CASE WHEN @month='' THEN ' and year(Start_time)= year(getdate()) and month(Start_time)>=month(getdate())' ELSE 
		'and year(Start_time)= year(getdate()) and month(Start_time)='''+@month+''' ' END;
	set @SQL+='group by pl.Agent_Name,Emp_Code;

	select SrNo,Agent_Name,Emp_Code,[Week 1],[Week 2],[Week 3],[Week 4],[Fatal Calls] from #temp1
	UNION ALL
	select NULL,''Total Sale Count'','''',SUM([Week 1]),SUM([Week 2]),SUM([Week 3]),SUM([Week 4]),sum([Fatal Calls]) from #temp1 order by 2'

	set @SQL+='select ROW_NUMBER() OVER (Order by CASE WHEN GROUPING(User_Name)=1 THEN 1 ELSE 0 END)SrNo,
			   CASE WHEN GROUPING(User_Name)=1 THEN ''Grand Total'' ELSE User_Name END [TL_Name],
			    SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
				from Process_'+@Process_id+'_Log pl left join User_Master on pl.Team_Leader_Id=User_Id left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
				where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
				IF(@tlid!='0')
				SET @sql+=' AND User_Name='''+@tlid+''' ';
				IF(@agentid!='0')
				SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
				IF(@qaid!='0')
				SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
				SET @sql+=CASE WHEN @month='' THEN ' and year(Start_time)= year(getdate()) and month(Start_time)>=month(getdate())' ELSE 
		'and year(Start_time)= year(getdate()) and month(Start_time)='''+@month+''' ' END;
			set @SQL+='group by User_Name with cube'

	EXEC(@SQL)
	PRINT(@SQL)
END


GO

/****** Object:  StoredProcedure [dbo].[QA_Team_Wise_Score_Report]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec QA_Team_Wise_Score_Report '0','0','0','','','TeamWiseScore','1','1'
CREATE PROC [dbo].[QA_Team_Wise_Score_Report] @tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), 
@reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10) 

AS BEGIN DECLARE @sql NVARCHAR(MAX)=''; 

SET @sql=' SELECT ROW_NUMBER() OVER (Order by TLname)SrNo,TLname TL_Name,AVG(External_Performance) Average_of_Call_Score_per,
case when AVG(External_Performance) >=95 then ''Green'' when AVG(External_Performance) between 90 and 94 then ''Yellow'' else ''Red'' end [Color] 
from QA_AgentDetails WHERE 1=1  ' 	

IF(@tlid!='0') 	SET @sql+=' AND TLname='''+@tlid+''' '; 	
IF(@agentid!='0') 	SET @sql+=' AND Agent_Name='''+@agentid+''' '; 	
IF(@qaid!='0') 	SET @sql+=' AND Auditor_Name='''+@qaid+''' '; 	
SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END; 	
SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END; 
SET @sql+=' GROUP BY TLname '; 
EXECUTE sp_executesql @SQL 
print (@SQL)
END 
GO

/****** Object:  StoredProcedure [dbo].[QA_Telecaller_WISE_REPORT]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- QA_Telecaller_WISE_REPORT '0','','0','2021-05-10','','0','4',''

CREATE PROC [dbo].[QA_Telecaller_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

DECLARE @sql NVARCHAR(MAX)='';
/*
SET @sql='select convert(varchar(10),start_time,103)[Date],pl.Agent_Name,sum(case when CallStatus=''Verification'' then 1 else 0 end)[Total leads generated] ,
sum(case when CallStatus=''Verification'' and isnull(QA_Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when CallStatus=''Verification'' and isnull(QA_Disposition,'''')='''' then 1 else 0 end)[Audit pending],
sum(case when CallStatus=''Verification'' and QA_Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when CallStatus=''Verification'' and QA_Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(case when CallStatus=''Verification'' and QA_Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from process_'+@Process_id+'_log pl
left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
where isnull(pl.Agent_Name,'''')!='''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
	IF(@tlid!='0')
	SET @sql+=' AND qa.TLname='''+@tlid+'''';
	IF(@agentid!='0')
	SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+='';
	SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Start_Time) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Start_Time) BETWEEN '''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+='group by pl.Agent_Name,convert(varchar(10),start_time,103) Order by convert(varchar(10),start_time,103),pl.Agent_Name';
*/

set @sql='with cte as(select Convert(varchar,Start_Time,103)Date,Agent_name,Count(1)[Total leads generated] from Process_'+@Process_id+'_log 
join user_master on Process_'+@Process_id+'_log.Team_Leader_Id=user_master.User_Id
where  CallStatus='''+@LeadCallStatus+''' '
+iif(@from is not null and @from!='',' and Start_time>='''+@from+'''','AND CONVERT(DATE,Start_Time) = CONVERT(DATE,GETDATE())')
+iif(@to is not null and @to!='',' and Start_time<='''+@to+'''','')
+iif(@tlid is not null and @tlid!='' and @tlid!='0',' and user_master.User_name='''+@tlid+'''','')+
+iif(@agentid is not null and @agentid!='' and @agentid!='0',' and Agent_name='''+@agentid+'''','')+
'
group by Convert(varchar,Start_Time,103),Agent_name
),
cte1 as(select convert(varchar,qa.Date_of_Audit,103) [Date],Agent_name, 
sum(case when isnull(Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when Disposition in(''Quality Rework Quality Reject'') then 1 else 0 end)[Audit pending],
sum(case when Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(case when Disposition=''Quality Reject'' then 1 else 0 end)[Reject] from QA_AgentDetails qa where  P_id='''+@Process_id+''' '

+iif(@from is not null and @from!='',' and Date_of_Audit>='''+@from+'''','AND CONVERT(DATE,Date_of_Audit) = CONVERT(DATE,GETDATE())')
+iif(@to is not null and @to!='',' and Date_of_Audit<='''+@to+'''','')
+iif(@tlid is not null and @tlid!='' and  @tlid!='0',' and TLname='''+@tlid+'''','') 
+iif(@agentid is not null and @agentid!='' and @agentid!='0',' and Agent_Name='''+@agentid+'''','') 
+iif(@qaid is not null and @qaid!='' and @qaid!='0',' and Auditor_Name='''+@qaid+'''','') + 

'
group by convert(varchar,qa.Date_of_Audit,103) ,qa.Agent_name)

select cte.Date,cte.Agent_name [Agent Name],[Total leads generated],isnull([Total leads audited],0)[Total leads audited],[Total leads generated]- isnull([Total leads audited],0)[Audit pending],
isnull([Audit ok],0)[Audit ok],isnull(Rework,0)Rework,isnull(Reject,0)Reject from cte left join cte1 on cte.Date=cte1.Date and cte.Agent_name=cte1.Agent_name
 '



	SET @sql+='order by cte.date'
EXECUTE sp_executesql @SQL
print (@sql)

END

--QA_Telecaller_WISE_REPORT '0','','0','2019-11-01','','0'


GO

/****** Object:  StoredProcedure [dbo].[QA_TL_WISE_REPORT]    Script Date: 25/06/2021 04:30:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- QA_TL_WISE_REPORT '','','','2021-06-01','','','4','1'

CREATE PROC [dbo].[QA_TL_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30)=NULL, @qaid VARCHAR(30)=NULL, @from VARCHAR(15), @to VARCHAR(15), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

DECLARE @sql NVARCHAR(MAX)='';

set @sql='with cte as(select Convert(varchar,Start_Time,103)Date,User_Name,Count(1)[Total leads generated] from Process_'+@Process_id+'_log 
join user_master on Process_'+@Process_id+'_log.Team_Leader_Id=user_master.User_Id
where  CallStatus='''+@LeadCallStatus+''' '
+iif(@from is not null and @from!='',' and Start_time>='''+@from+'''','AND CONVERT(DATE,Start_Time) = CONVERT(DATE,GETDATE())')
+iif(@to is not null and @to!='',' and Start_time<='''+@to+'''','')
+iif(@tlid is not null and @tlid!='' and @tlid!='0',' and user_master.User_name='''+@tlid+'''','')+
'
group by Convert(varchar,Start_Time,103),User_Name
),
cte1 as(select convert(varchar,qa.Date_of_Audit,103) [Date],qa.TLname TLname, 
sum(case when isnull(Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when Disposition in(''Quality Rework Quality Reject'') then 1 else 0 end)[Audit pending],
sum(case when Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(case when Disposition=''Quality Reject'' then 1 else 0 end)[Reject] from QA_AgentDetails qa where  P_id='''+@Process_id+''' '

+iif(@from is not null and @from!='',' and Date_of_Audit>='''+@from+'''','AND CONVERT(DATE,Date_of_Audit) = CONVERT(DATE,GETDATE())')
+iif(@to is not null and @to!='',' and Date_of_Audit<='''+@to+'''','')
+iif(@tlid is not null and @tlid!='' and  @tlid!='0',' and TLname='''+@tlid+'''','') 
+iif(@agentid is not null and @agentid!='' and @agentid!='0',' and Agent_Name='''+@agentid+'''','') 
+iif(@qaid is not null and @qaid!='' and @qaid!='0',' and Auditor_Name='''+@qaid+'''','') + 

'
group by convert(varchar,qa.Date_of_Audit,103) ,qa.TLname
)
select cte.Date,isnull(TLname,User_Name)TLname,[Total leads generated],isnull([Total leads audited],0)[Total leads audited],[Total leads generated]- isnull([Total leads audited],0)[Audit pending],
isnull([Audit ok],0)[Audit ok],isnull(Rework,0)Rework,isnull(Reject,0)Reject from cte left join cte1 on cte.Date=cte1.Date and cte.User_Name=cte1.TLname
 '



	SET @sql+='order by cte.date'

EXECUTE sp_executesql @SQL
print(@sql)
END

--QA_TL_WISE_REPORT '','','','','4','1'


GO




